package com.supermap.dataservice.alltype.dao;

import com.supermap.dubbo.api.alltype.entity.AllType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

/**
 * @author lxs
 * @Title:当天各类(咨询,农情,灾情,产品)数据的实时 数据访问层
 * @Description:
 * @date 2018/8/2  11:52
 */
@Mapper
public interface AllTypeDao {

    @SelectProvider(type = AllTypeSql.class, method = "getAllTypeToAgrListSql")
//    @Select("select t1.id,t1.`longitude` AS lon,t1.`latitude` AS lat,t2.areaname from  t_agrinfo t1 INNER JOIN t_area t2 on t1.areacode=t2.areacode " +
//            "where t1.areacode like #{areaCode} and date(t1.createtime)=date(now()) ")
    List<AllType> getAllTypeToAgrList(@Param("areaCode")String areaCode);

//    @Select("select t1.id,t1.`longitude` AS lon,t1.`latitude` AS lat,t2.areaname  from  t_disaster t1 INNER JOIN t_area t2 on t1.areacode=t2.areacode " +
//            "where t1.areacode like #{areaCode} and date(t1.createtime)=date(now()) ")
    @SelectProvider(type = AllTypeSql.class, method = "getAllTypeToDisListSql")
    List<AllType> getAllTypeToDisList(@Param("areaCode")String areaCode);

    @SelectProvider(type = AllTypeSql.class, method = "getAreaByCodeSQL")
    List<AllType>  getAreaByCode(@Param("areaCode")String areaCode);



//    @Select("select t1.id,t1.lon,t1.lat,t2.areaname from  t_advisoryinfo t1 INNER JOIN t_area t2 on t1.areacode=t2.areacode " +
//            "where t1.areacode like #{areaCode} and date(t1.uploadTime)=date(now()) ")
    @SelectProvider(type = AllTypeSql.class, method = "getAllTypeToAdvListSql")
    List<AllType> getAllTypeToAdvList(@Param("areaCode")String areaCode);

    //获取当前区域的农田各类统计
    @Select("select t1.id,t4.type as name,count(t4.type) as num from t_farmland t1 INNER JOIN t_farmcrops t2 on t1.id=t2.farmland\n" +
            "INNER JOIN t_crop t3 on t2.crop=t3.code\n" +
            "INNER JOIN t_crop_type t4 on t4.cropid=t3.id where t1.code like #{areaCode} group by t4.type")
    List<AllType> getFarmCropNumByAreaCode(@Param("areaCode")String areaCode);



    /**
     * 内部类 编写动态sql
     *
     * @author lxs
     */
    public class AllTypeSql {
        /**
         * gro 区域
         * */
        public String getAreaByCodeSQL(Map<String, Object> map){
            String areaCode = (String)map.get("areaCode");
            StringBuilder sql = new StringBuilder();
            sql.append("select areacode as id,areaname,longitude,latitude from t_area where   length(areacode)<=4 ");
            if(areaCode!=null){
                sql.append(" and areacode like concat("+areaCode+",'%')");
            }
            return sql.toString();
        }
        public String getAllTypeToAgrListSql(Map<String, Object> map) {
            String areaCode = (String)map.get("areaCode");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
//            sql.append("select t1.id,t1.`longitude`,t1.`latitude`,t2.areaname from  t_agrinfo t1 INNER JOIN t_area t2 on t1.areacode=t2.areacode where date(t1.createtime)=date(now()) ");
//            if(areaCode !=null && !areaCode.equals("") && !areaCode.equals("5")){
//                sql.append(" and t1.areacode like '"+areaCode+"%'");
//            }
            //modify by zhoujian
            sql.append("SELECT t1.id,t1.descript as title,t1.`longitude`,t1.`latitude`,t2.areaname,u.username as name,t1.createtime as creatime\n" +
                    "FROM t_agrinfo t1\n" +
                    "INNER JOIN t_area t2 ON t1.areacode=t2.areacode inner join t_user u on u.id = t1.userId\n" +
                    "WHERE DATE(t1.createtime)= DATE(NOW())");
            if(areaCode !=null && !areaCode.equals("") && !areaCode.equals("5")){
                sql.append(" and t1.areacode like '"+areaCode+"%'");
            }
            return sql.toString();
        }

        public String getAllTypeToDisListSql(Map<String, Object> map) {
            String areaCode = (String)map.get("areaCode");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
//            sql.append("select t1.id,t1.`longitude` ,t1.`latitude` ,t2.areaname  from  t_disaster t1 INNER JOIN t_area t2 on t1.areacode=t2.areacode where  date(t1.createtime)=date(now()) ");
//            if(areaCode !=null && !areaCode.equals("") && !areaCode.equals("5")){
//                sql.append(" and t1.areacode like '"+areaCode+"%'");
//            }
            //modify by zhoujian
            sql.append("SELECT t1.id, t1.descript as title ,t1.`longitude`,t1.`latitude`,t2.areaname,u.username as name, t1.createtime as creatime\n" +
                    "FROM t_disaster t1\n" +
                    "INNER JOIN t_area t2 ON t1.areacode=t2.areacode\n" +
                    "inner join t_user u on u.id = t1.userId\n" +
                    "WHERE DATE(t1.createtime)= DATE(NOW())");
            if(areaCode !=null && !areaCode.equals("") && !areaCode.equals("5")){
                sql.append(" and t1.areacode like '"+areaCode+"%'");
            }

            return sql.toString();
        }

        public String getAllTypeToAdvListSql(Map<String, Object> map) {
            String areaCode = (String)map.get("areaCode");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            //modify by zhoujian
//            sql.append("select t1.id,t1.lon as longitude,t1.lat as latitude,t2.areaname from  t_advisoryinfo t1 INNER JOIN t_area t2 on t1.areacode=t2.areacode where date(t1.uploadTime)=date(now()) ");
//            if(areaCode !=null  && !areaCode.equals("") && !areaCode.equals("5")){
//                sql.append(" and t1.areacode like '"+areaCode+"%'");
//            }
            sql.append("SELECT t1.id,t1.question as title,t1.lon AS longitude,t1.lat AS latitude,t2.areaname,t1.uploadTime as creatime ,u.username as name\n" +
                    "FROM t_advisoryinfo t1\n" +
                    "INNER JOIN t_area t2 ON t1.areacode=t2.areacode left join t_user u on u.id = t1.userId\n" +
                    "WHERE DATE(t1.uploadTime)= DATE(NOW())");
            if(areaCode !=null  && !areaCode.equals("") && !areaCode.equals("5")){
                sql.append(" and t1.areacode like '"+areaCode+"%'");
            }
            return sql.toString();
        }

    }
}
